package com.ncloud.crm.mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.ncloud.crm.entity.order.Work;

/**
 * Created by shiqiang on 2017/4/11.
 */
@Mapper
public interface WorkMapper {
    @Select(value = { "select a.*,f_get_label('WORK_STATE',a.work_state) as work_state_name from bs_o_work a where a.work_id = #{workId}" })
    @Results({ @Result(column = "work_id", property = "workId"),@Result(column = "node_id", property = "nodeId"),
            @Result(column="construct_item",property="constructItem"),@Result(column="construct_user",property="constructUser"),
            @Result(column="construct_time",property="constructTime"),@Result(column="remark",property="remark"),
            @Result(column="finish_time",property="finishTime"),@Result(column="order_id",property="orderId"),
            @Result(column="work_state",property="workState"),@Result(column="work_state_name",property="workStateName") })
    public Work getWorkById(Long workId);
    
    @Select(value = { "select a.*,f_get_label('WORK_STATE',a.work_state) as work_state_name from bs_oh_work a where a.work_id = #{workId}" })
    @Results({ @Result(column = "work_id", property = "workId"),@Result(column = "node_id", property = "nodeId"),
            @Result(column="construct_item",property="constructItem"),@Result(column="construct_user",property="constructUser"),
            @Result(column="construct_time",property="constructTime"),@Result(column="remark",property="remark"),
            @Result(column="finish_time",property="finishTime"),@Result(column="order_id",property="orderId"),
            @Result(column="work_state",property="workState"),@Result(column="work_state_name",property="workStateName") })
    public Work getHisWorkById(Long workId);
    
    @Insert(value = "insert into bs_o_work(work_id,node_id,construct_item,construct_user,construct_time," +
            "remark,finish_time,order_id,work_state,create_time) values(#{workId},#{nodeId}," +
            "#{constructItem},#{constructUser},#{constructTime},#{remark},#{finishTime},#{orderId},#{workState},#{createTime})")
    public int insertWork(Work work);
    @Select(value = { "select a.work_id,b.order_id,c.chnl_id,c.chnl_name,e.name as sale_name,b.cust_id,b.cust_name,"
    		+ " f_date_format(a.construct_time) as construct_time,f_date_format(b.create_date) as create_date,"
    		+ " b.prod_id,f_get_prodName(b.prod_id) as prod_name,f_get_label('WORK_STATE',a.work_state) as work_state,"
    		+ " (select if(count(1)>0,'变更','新装') from bs_node where cust_id = b.cust_id) as change_flag, "
    		+ " (select if(count(1)>0,'2','1') from bs_node where cust_id = b.cust_id) as change_flag_code "
    		+ " from bs_o_work a, bs_o_order b, sys_chnl c,sys_sales d,sys_user e"
    		+ " where a.order_id = b.order_id and c.chnl_id =d.chnl_id and b.create_user = d.user_id"
    		+ " and d.user_id=e.id and (a.work_state = #{workState} or #{workState} is null)" })
    @Results({ @Result(javaType=String.class, column = "work_id", property = "workId"), @Result(javaType=String.class, column = "order_id", property = "orderId"),
            @Result(javaType=String.class, column = "chnl_id", property = "chnlId"),@Result(column = "chnl_name", property = "chnlName"),
            @Result(column="sale_name",property="saleName"),@Result(javaType=String.class, column="cust_id",property="custId"),
            @Result(column="cust_name",property="custName"),@Result(column="work_state",property="workState"),
            @Result(column="construct_time",property="constructTime"),@Result(javaType=String.class, column="prod_id",property="prodId"),
            @Result(column="prod_name",property="prodName"),@Result(column="create_date",property="createDate"),
            @Result(column="change_flag",property="changeFlag")})
    public List<Map<String,Object>> getAllWorkds(String workState);
    
    @Update("update bs_o_work set construct_user=#{constructUser},construct_time=#{constructTime},"
    		+ " construct_item=#{constructItem},finish_time=#{finishTime},work_state=#{workState} "
    		+ " where work_id=#{workId}")
	public int update(Work work);

    @Update("update bs_o_work set work_state=#{workState},construct_user=IFNULL(#{constructUser},construct_user),"
    		+ " construct_time=IFNULL(#{constructTime},construct_time) where work_id=#{workId} and work_state='1'")
    int updateWorkState(Work work);
    @Select(value = { "select * from bs_o_work where order_id = #{orderId}" })
    @Results({ @Result(column = "work_id", property = "workId"),@Result(column = "node_id", property = "nodeId"),
            @Result(column="construct_item",property="constructItem"),@Result(column="construct_user",property="constructUser"),
            @Result(column="construct_time",property="constructTime"),@Result(column="remark",property="remark"),
            @Result(column="finish_time",property="finishTime"),@Result(column="order_id",property="orderId"),
            @Result(column="work_state",property="workState")})
	public List<Work> getWorksByOrderId(long orderId);
    @Select(value = { "select * from bs_o_work where order_id = #{orderId} and work_state =#{workState}" })
    @Results({ @Result(column = "work_id", property = "workId"),@Result(column = "node_id", property = "nodeId"),
            @Result(column="construct_item",property="constructItem"),@Result(column="construct_user",property="constructUser"),
            @Result(column="construct_time",property="constructTime"),@Result(column="remark",property="remark"),
            @Result(column="finish_time",property="finishTime"),@Result(column="order_id",property="orderId"),
            @Result(column="work_state",property="workState") })
	public List<Work> getWorksByOrderIdAndState(@Param("orderId")long orderId, @Param("workState")char workState);
	
    @Insert(value = "insert into bs_oh_work(work_id,node_id, construct_item, construct_user,"
    		+ " construct_time, remark, finish_time, order_id, work_state)" 
    		+ " select work_id, node_id, construct_item, construct_user, construct_time,"
            + " remark, finish_time, order_id, work_state from bs_o_work where order_id = #{order_id}")
    public int insertHisWork(Long orderId);
    
    @Delete("delete from bs_o_work where order_id = #{orderId}")
	public int deleteByOrderId(Long orderId);
   
    
    
  //通过子合同编码查看工单施工状态(施工中、施工完成)
  	@Select(value = {"select a.* from bs_o_work a left join bs_o_contract b on a.order_id = b.order_id where "
  			+ "(a.work_state = '2' or a.work_state = '3') and b.child_contract_inst_id = #{1}"})
  	@Results({ @Result(column = "work_id", property = "workId"),
  			@Result(column = "node_id", property = "nodeId"),@Result(column = "construct_item", property = "constructItem"),
  			@Result(column = "construct_user", property = "constructUser"),@Result(column = "construct_time", property = "constructTime"),
  			@Result(column="remark",property="remark"),@Result(column="finish_time",property="finishTime"),
  			@Result(column="order_id",property="orderId"),@Result(column="work_state",property="workState"),
  			@Result(column="create_time",property="createTime"),})
  	public List<Work> getWorksByContractInstId(String contractInstId);
  	
  	@Select(value = { "select a.work_id,b.order_id,c.chnl_id,c.chnl_name,e.name as sale_name,b.cust_id,b.cust_name,"
    		+ " f_date_format(a.construct_time) as construct_time,f_date_format(b.create_date) as create_date,"
    		+ " b.prod_id,f_get_prodName(b.prod_id) as prod_name,work_state,f_get_label('WORK_STATE',a.work_state) as work_state_name,"
    		+ " (select if(count(1)>1,'变更','新装') from bs_o_node where work_id = a.work_id) as change_flag, "
    		+ " (select if(count(1)>1,'2','1') from bs_o_node where work_id = a.work_id) as change_flag_code "
    		+ " from bs_o_work a, bs_o_order b, sys_chnl c,sys_sales d,sys_user e"
    		+ " where a.order_id = b.order_id and c.chnl_id =d.chnl_id and b.create_user = d.user_id and e.id = d.user_id"
    		+ " and b.order_id =#{orderId} and (b.order_state = #{orderState} or #{orderState} is null)" })
    @Results({ @Result(javaType=String.class, column = "work_id", property = "workId"), @Result(javaType=String.class, column = "order_id", property = "orderId"),
            @Result(javaType=String.class, column = "chnl_id", property = "chnlId"),@Result(column = "chnl_name", property = "chnlName"),
            @Result(column="sale_name",property="saleName"),@Result(javaType=String.class, column="cust_id",property="custId"),
            @Result(column="cust_name",property="custName"),@Result(column="work_state",property="workState"),
            @Result(column="construct_time",property="constructTime"),@Result(javaType=String.class, column="prod_id",property="prodId"),
            @Result(column="prod_name",property="prodName"),@Result(column="create_date",property="createDate"),
            @Result(column="change_flag",property="changeFlag"),@Result(column="work_state_name",property="workStateName")})
	public List<Map<String, Object>> getWorksByOrder(@Param("orderId")long orderId, @Param("orderState")String orderState);
  	
  	@Select(value = { "select a.work_id,b.order_id,c.chnl_id,c.chnl_name,e.name as sale_name,b.cust_id,b.cust_name,"
    		+ " f_date_format(a.construct_time) as construct_time,f_date_format(b.create_date) as create_date,"
    		+ " b.prod_id,f_get_prodName(b.prod_id) as prod_name,work_state,f_get_label('WORK_STATE',a.work_state) as work_state_name,"
    		+ " (select if(count(1)>1,'变更','新装') from bs_o_node where work_id = a.work_id) as change_flag, "
    		+ " (select if(count(1)>1,'2','1') from bs_o_node where work_id = a.work_id) as change_flag_code "
    		+ " from bs_oh_work a, bs_oh_order b, sys_chnl c,sys_sales d,sys_user e"
    		+ " where a.order_id = b.order_id and c.chnl_id =d.chnl_id and b.create_user = d.user_id and e.id = d.user_id"
    		+ " and b.order_id =#{orderId} and (b.order_state = #{orderState} or #{orderState} is null)" })
    @Results({ @Result(javaType=String.class, column = "work_id", property = "workId"), @Result(javaType=String.class, column = "order_id", property = "orderId"),
            @Result(javaType=String.class, column = "chnl_id", property = "chnlId"),@Result(column = "chnl_name", property = "chnlName"),
            @Result(column="sale_name",property="saleName"),@Result(javaType=String.class, column="cust_id",property="custId"),
            @Result(column="cust_name",property="custName"),@Result(column="work_state",property="workState"),
            @Result(column="construct_time",property="constructTime"),@Result(javaType=String.class, column="prod_id",property="prodId"),
            @Result(column="prod_name",property="prodName"),@Result(column="create_date",property="createDate"),
            @Result(column="change_flag",property="changeFlag"),@Result(column="work_state_name",property="workStateName")})
	public List<Map<String, Object>> getHisWorksByOrder(@Param("orderId")long orderId, @Param("orderState")String orderState);

	@Update("update bs_o_work set work_state=#{workState} where work_id=#{workId}")
	int updateWorkState2(@Param("workId")Long workId, @Param("workState")char workState);

	/*查询不是当前工单状态的数量3：工单完工，5：工单邮寄中*/
	@Select(value = {"select count(1) as num from bs_o_work a where  EXISTS (select work_id from bs_o_work b where   "
			+ "b.order_id =a.order_id and a.work_state=b.work_state and b.work_state not in('3','5')) and a.order_id = #{1}"})
	public int GetNotWorkStateCount(Long orderId);

}
